﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_AddCleanPlan

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal WorkHeaderID As Integer, ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.*,B.WorkHour,B.WorkTimes,B.VolunteerId,C.VolunteerName ")
        sql.AppendFormat(",oo.VOrganizationName,oo.DirectUOrganizationID ")
        sql.AppendFormat("FROM WorkRecord A ")
        sql.AppendFormat("INNER JOIN WorkHourRecord B ON A.RecordID=B.RecordID AND B.WorkType='CleanPlan' ")
        sql.AppendFormat("INNER JOIN Volunteer C on B.VolunteerID = C.VolunteerID ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID ,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on B.VolunteerID=oo.VolunteerID  ")
        'sql.AppendFormat("INNER JOIN Village r on C.VillageID = r.RegionID ")
        sql.AppendFormat("WHERE C.Active = 'Y' and WorkHeaderID={0} ", WorkHeaderID)
        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        'sql.AppendFormat("AND (C.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (C.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (C.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0}  ) ", VOrganizationID)
        End If
        If VolunteerId <> "" Then
            sql.AppendFormat("AND (C.VolunteerId = '{0}' ) ", VolunteerId)

        End If
        If VolunteerName <> "" Then
            sql.AppendFormat("AND (C.VolunteerName like '%{0}%' ) ", VolunteerName)
        End If
        If TEL <> "" Then
            sql.AppendFormat("AND (((C.MobilePhone like '%{0}%' ) or (C.OfficePhone like '%{0}%' ))) ", TEL)

        End If

        Return dat.GetData(sql)

    End Function

    'Update 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)> _
    Public Function BLL_Update(ByVal original_RecordID As Integer, ByVal WorkHour As Single, ByVal WorkTimes As Integer, ByVal LoginUser As String) As Integer
        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.AppendFormat("UPDATE WorkRecord ")
            sql.AppendFormat("SET UpdateDate=getdate() ")
            sql.AppendFormat(",UpdateBy='{0}' ", LoginUser)
            sql.AppendFormat("WHERE RecordID={0}; ", original_RecordID)

            sql.AppendFormat("UPDATE WorkHourRecord ")
            sql.AppendFormat("SET WorkHour={0} ", WorkHour)
            sql.AppendFormat(",WorkTimes={0} ", WorkTimes)
            sql.AppendFormat("WHERE RecordID={0} AND WorkType='CleanPlan'; ", original_RecordID)

            dat.AccData(sql)

            scope.Complete()

            Return 1

        End Using

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Delete(ByVal original_RecordID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.Length = 0
            sql.AppendFormat("DELETE WorkRecord WHERE RecordID={0}; ", original_RecordID)
            sql.AppendFormat("DELETE WorkHourRecord WHERE RecordID={0} AND WorkType='CleanPlan'; ", original_RecordID)

            dat.AccData(sql)

            scope.Complete()

        End Using

    End Sub

End Class
